﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using Bamuca.Codigo.DTO;
using System.Data;

namespace Bamuca.Codigo.DAO
{
    public class NaipeDao
    {
        MySqlConnection conn = ConexaoDao.SqlConectar();
        MySqlCommand cmd = new MySqlCommand();

        public void Inserir(NaipeDto _naipeDto)
        {
            try
            {
                cmd.Connection = conn;
                cmd.CommandText = "insert into naipes (nome, status)" +
                                  "values (@nome, @status)";
                cmd.Parameters.AddWithValue("@nome", _naipeDto.Nome);
                cmd.Parameters.AddWithValue("@status", _naipeDto.Status);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }

        public void Alterar(NaipeDto _naipeDto)
        {
            try
            {
                cmd.Connection = conn;
                cmd.CommandText = "UPDATE naipes SET (nome = @nome, " +
                                   "status = @status " +
                                   "WHERE idnaipe = @id";

                cmd.Parameters.AddWithValue("@nome", _naipeDto.Nome);
                cmd.Parameters.AddWithValue("@status", _naipeDto.Status);
                cmd.Parameters.AddWithValue("@id", _naipeDto.IdNaipe);

                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }

        }

        public void Excluir(NaipeDto _naipeDto)
        {
            try
            {
                cmd.Connection = conn;
                cmd.CommandText = "delete naipes WHERE idnaipe = @id";
                cmd.Parameters.AddWithValue("@id", _naipeDto.IdNaipe);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }

        }

        public DataTable ConsultarTodosPorParametro(NaipeDto _naipeDto)
        {
            MySqlDataAdapter dtAdap;
            DataTable dtTable = new DataTable();

            string _strSql = "SELECT idnaipe AS Id, Nome, IF(status = 1, 'Ativo', 'Inativo') AS Status FROM naipes WHERE 1=1 ";
            if (_naipeDto.IdNaipe > 0)
            {
                _strSql = _strSql + "AND idnaipe = " + _naipeDto.IdNaipe + " ";
            }
            if (_naipeDto.Nome != null)
            {
                _strSql = _strSql + "AND nome LIKE '%" + _naipeDto.Nome + "%' ";
            }
            if (_naipeDto.Status != 2)
            {
                if (_naipeDto.Status == 1) { _strSql = _strSql + "AND status = 1 "; }
                else { _strSql = _strSql + "AND status = 0 "; }
            }
            try
            {
                dtAdap = new MySqlDataAdapter(_strSql, conn);
                dtAdap.Fill(dtTable);
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return dtTable;
        }

        public NaipeDto ConsultarUmPorParametro(NaipeDto _naipeDto)
        {
            MySqlDataReader dr;
            NaipeDto _naipe = new NaipeDto();

            string _strSql = "SELECT * FROM naipe WHERE 1=1 ";

            if (_naipeDto.IdNaipe > 0)
            {
                _strSql = _strSql + "AND idnaipe = " + _naipeDto.IdNaipe + " ";
            }

            if (_naipeDto.Nome != string.Empty)
            {
                _strSql = _strSql + "AND nome LIKE '%" + _naipeDto.Nome + "'% ";
            }

            if (_naipeDto.Status != null)
            {
                if (_naipeDto.Status == 1) { _strSql = _strSql + "AND status = 1 "; }
                else { _strSql = _strSql + "AND status = 0 "; }
            }

            try
            {
                cmd.Connection = conn;
                cmd.CommandText = _strSql;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    _naipe.IdNaipe = Convert.ToInt32(dr["idnaipe"]);
                    _naipe.Nome = dr["nome"].ToString();
                    _naipe.Status = Convert.ToInt32(dr["status"]);
                }

            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return _naipe;
        }
    }
}